package collage.SUBJECT.TYPE;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class SUBJECT_TYPE_DAO implements ISUBJECT_TYPE_DAO {

	
	
	// URL / SQL 클래스 분리시키기 
		private String DRV = "oracle.jdbc.driver.OracleDriver";
		private String URL = "jdbc:oracle:thin:cjhmasterola/cjhmasterola@112.216.253.125:1521:xe";
		private String insertSQL = "insert into SUBJECT_TYPE values( sbj.nextval, ?,  ? )";
		private String selectSQL = "select * from SUBJECT_TYPE";
		private String updateSQL = "update SUBJECT_TYPE set sub_type_name = ? where = sub_type_no ";
		private String deleteSQL = "delete SUBJECT_TYPE  where sub_type_no = ? ";
				
		@Override
		public boolean insertSubject( SUBJECT_TYPE_VO s ) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			boolean result = false;
			
			
			
			try {
				conn = openDatabase();
				pstmt = conn.prepareStatement( insertSQL );
					pstmt.setString(1, s.getSub_type_no() );
					pstmt.setString(2, s.getSub_type_name() );
				
				pstmt.executeUpdate();	
				result=true;
				
			} catch ( Exception ex ){
				ex.printStackTrace();
			} finally {
				closeDatabase(conn, pstmt, null);
			}
			
					
			return result;
		}
		@Override
		public List<SUBJECT_TYPE_VO> selectSubject() {
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			List<SUBJECT_TYPE_VO> result = new ArrayList<>();			// 동적배열
			
			try {
					conn = openDatabase();
					pstmt = conn.prepareStatement(selectSQL);
					rs = pstmt.executeQuery();
					while( rs.next() ) {
						
						SUBJECT_TYPE_VO s = new SUBJECT_TYPE_VO();
						s.setType_tr( rs.getString( "type_tr") ); 
						s.setSub_type_no( rs.getString( "sub_type_no") );
						s.setSub_type_name(rs.getString("sub_type_name"));
					
										
						result.add( s );
											
					}
					
					
			} catch ( Exception ex ) {
				ex.printStackTrace();
				
			}finally {
				closeDatabase(conn, pstmt, rs);
				
			}
			
			return result;		
			
		}
		@Override
		public boolean updateSubject(SUBJECT_TYPE_VO s) {
			
			Connection conn = null;
			PreparedStatement pstmt = null;
			boolean result = false;
			
			try{
				conn = openDatabase();
				pstmt = conn.prepareStatement(updateSQL);
					pstmt.setString(1, s.getSub_type_name() );
					pstmt.setString(2, s.getSub_type_no() );
				int cnt = pstmt.executeUpdate();
				if ( cnt == 0 ) {
					System.out.println( "명령은 잘 실행됐지만, 수정된 데이타너는 없음" );
				}
				else {
					result = true;
				}
				
			} catch ( Exception ex ){
				ex.printStackTrace();
			} finally {
				closeDatabase(conn, pstmt, null);
			}
			
			return result;
		}

		@Override
		public boolean deleteSubject(SUBJECT_TYPE_VO s) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			boolean result = false;
			
			try {
				conn = openDatabase();
				pstmt = conn.prepareStatement(deleteSQL);
						pstmt.setString( 1, s.getSub_type_no() );
						
				int cnt = pstmt.executeUpdate();
				if ( cnt == 0 ){ 
					System.out.println( "명령은 잘 실행됐지만, 삭제된 데이타는 없음" );
					
				}
				else {
					result = true;
				}
				
			} catch ( Exception ex ) {
				ex.printStackTrace();
			} finally {
				closeDatabase(conn, pstmt, null);			
			}
			
			return result;
		}
		
		
		
		private Connection openDatabase() {
			try{	
				Class.forName(DRV);
				return DriverManager.getConnection(URL);			
			}catch( Exception ex ){
				ex.printStackTrace();
			}
			
			return  null;
		}

		
		private void closeDatabase( Connection c, PreparedStatement p, ResultSet r ){
			if ( r != null ) try { r.close(); r = null; } catch( Exception ex ) {}
			if ( p != null ) try { p.close(); p = null; } catch( Exception ex ) {}
			if ( c != null ) try { c.close(); c = null; } catch( Exception ex ) {}
			
			
		}
		

		
		
	
	
	
} // c
